#!/usr/bin/env python3

import codecs
import json
import os
import sys

sys.path.append('..')

from PySide6.QtCore import QIODevice, QBuffer, QByteArray
from PySide6.QtGui import QImage
from PySide6.QtSql import QSqlQuery
from PySide6.QtWidgets import QApplication

from Philatelist.Collection.CollectionFields import CollectionFieldsBase
from Philatelist.Reference.Reference import Reference

def convertImage(file_name):
    image = QImage(file_name)
    if image.load(file_name):
        ba = QByteArray()
        buffer = QBuffer(ba)
        buffer.open(QIODevice.WriteOnly)
        image.save(buffer, 'png')
        return ba
    else:
        return None

app = QApplication(sys.argv)
fields = CollectionFieldsBase()
ref = Reference(fields)

file = "reference/countries.json"
json_data = codecs.open(file, "r", "utf-8").read()
countries = json.loads(json_data)

file = "reference/countries_en.json"
json_data = codecs.open(file, "r", "utf-8").read()
countries_en = json.loads(json_data)

file = open(os.path.join(os.path.dirname(__file__), "i18n/langs"))
langs = [x.strip('\n') for x in file.readlines()]
file.close()

for lang in langs:
    print(lang)
    dst_file = "reference_%s.ref" % lang
    try:
        os.remove(dst_file)

    except FileNotFoundError:
        pass

    ref.open(dst_file, interactive=False)
    ref.load()

    file = "reference/countries_%s.json" % lang
    json_data = codecs.open(file, "r", "utf-8").read()
    translations = json.loads(json_data)

    region_section = ref.section('region')
    country_section = ref.section('country')
    ref.db.transaction()
    for i, region in enumerate(countries['regions'], 1):
        pos = countries_en['region'].index(region['name'])
        region_name = translations['region'][pos]
        region_section.addItem(region_name)
        for country in region['countries']:
            pos = countries_en['country'].index(country['name'])
            country_name = translations['country'][pos]
            query = QSqlQuery(country_section.db)
            query.prepare("INSERT INTO ref_country (parentid, value, icon) VALUES (?, ?, ?)")
            query.addBindValue(i)
            query.addBindValue(country_name)
            country_code = country['code']
            icon = convertImage("flags/%s.png" % country_code.lower())
            query.addBindValue(icon)
            query.exec()
            country_id = query.lastInsertId()
            for section_name in ('cancellation', 'currency', 'perforation', 'printing', 'series', 'theme',  'type', 'format'):
                if section_name in country:
                    for item in country[section_name]:
                        pos = countries_en[section_name].index(item)
                        item_name = translations[section_name][pos]
                        query = QSqlQuery(ref.section(section_name).db)
                        query.prepare("INSERT INTO ref_%s (parentid, value) VALUES (?, ?)" % section_name)
                        query.addBindValue(country_id)
                        query.addBindValue(item_name)
                        query.exec()
                else:
                    query = QSqlQuery(ref.section(section_name).db)
                    query.prepare("INSERT INTO ref_%s (parentid, value) VALUES (?, ?)" % section_name)
                    query.addBindValue(country_id)
                    query.addBindValue("")
                    query.exec()

    ref.db.commit()

    file = "reference/materials.json"
    json_data = codecs.open(file, "r", "utf-8").read()
    materials = json.loads(json_data)

    file = "reference/materials_en.json"
    json_data = codecs.open(file, "r", "utf-8").read()
    materials_en = json.loads(json_data)

    file = "reference/materials_%s.json" % lang
    json_data = codecs.open(file, "r", "utf-8").read()
    materials_translations = json.loads(json_data)

    country_section = ref.section('country')
    material_section = ref.section('material')
    material_type_section = ref.section('material_type')
    material_thickness_section = ref.section('thickness')

    ref.db.transaction()

    for i, country in enumerate(materials['countries'], 1):
        pos = countries_en['country'].index(country['country'])
        country_name = translations['country'][pos]
        query = QSqlQuery(country_section.db)
        query.prepare("SELECT id FROM ref_country WHERE value=?")
        query.addBindValue(country_name)
        query.exec()
        if query.first():
            country_id = query.record().value(0)
            for material in country['materials']:
                pos = materials_en['name'].index(material['name'])
                material_name = materials_translations['name'][pos]
                query = QSqlQuery(material_section.db)
                query.prepare("INSERT INTO ref_material (parentid, value) VALUES (?, ?)")
                query.addBindValue(country_id)
                query.addBindValue(material_name)
                query.exec()
                material_id = query.lastInsertId()
                for section_name in ('material_type', 'thickness'):
                    if section_name in material:
                        for item in material[section_name]:
                            pos = materials_en[section_name].index(item)
                            item_name = materials_translations[section_name][pos]
                            query = QSqlQuery(ref.section(section_name).db)
                            query.prepare("INSERT INTO ref_%s (parentid, value) VALUES (?, ?)" % section_name)
                            query.addBindValue(material_id)
                            query.addBindValue(item_name)
                            query.exec()
                    else:
                        query = QSqlQuery(ref.section(section_name).db)
                        query.prepare("INSERT INTO ref_%s (parentid, value) VALUES (?, ?)" % section_name)
                        query.addBindValue(material_id)
                        query.addBindValue("")
                        query.exec()

    ref.db.commit()

    file = "reference/reference_%s.json" % lang
    if not os.path.isfile(file):
        file = "reference/reference_en.json"

    json_data = codecs.open(file, "r", "utf-8").read()
    data = json.loads(json_data)

    ref.db.transaction()

    for section_name in sorted(data.keys()):
        section = ref.section(section_name)
        for value in data[section_name]:
            section.addItem(value)

    place = ref.section('pay_place')

    if lang == 'ru':
        place.addItem('АО Марка', convertImage('places/marka.png'))
        place.addItem('АукционЪ.СПб', convertImage('places/spb.png'))
        place.addItem('Мешок', convertImage('places/meshok.ico'))
        place.addItem('Молоток', convertImage('places/meshok.ico'))
        place.addItem('Монетник', convertImage('places/monetnik.ico'))
    else:
        place.addItem('JSC Marka', convertImage('places/marka.png'))
        place.addItem('Auction.SPB', convertImage('places/spb.png'))
        place.addItem('Meshok', convertImage('places/meshok.ico'))
        place.addItem('Molotok', convertImage('places/molotok.ico'))
        place.addItem('Monetnik', convertImage('places/monetnik.ico'))

    place.addItem('Auction.ru', convertImage('places/newauction.png'))
    place.addItem('aucland', convertImage('places/aucland.ico'))
    place.addItem('Avito', convertImage('places/avito.png'))
    place.addItem('Colnect', convertImage('places/colnect.png'))
    place.addItem('eBay', convertImage('places/ebay.png'))
    place.addItem('Ozon', convertImage('places/ozon.png'))
    place.addItem('Wolmar', convertImage('places/wolmar.ico'))
    place.addItem('Zagorsky-Stamps', convertImage('places/zagorsky-stamps.png'))
    place.addItem('Zetika', convertImage('places/zetika.png'))
    place.addItem('Stamprus')

    place.model.submitAll()

    glue_color = ref.section('glue_color')
    if lang == 'ru':
        glue_color.addItem('Без клея')
        glue_color.addItem('Частично смыт')
        glue_color.addItem('Прозрачный', convertImage('colors/transparent.png'))
        glue_color.addItem('Белый', convertImage('colors/white.png'))
        glue_color.addItem('Бледно-желтый', convertImage('colors/pale-yellow.png'))
        glue_color.addItem('Желтый', convertImage('colors/yellow.png'))
        glue_color.addItem('Темно-желтый', convertImage('colors/dark-yellow.png'))
    else:
        glue_color.addItem('Without glue')
        glue_color.addItem('Partially washed off')
        glue_color.addItem('Transparent', convertImage('colors/transparent.png'))
        glue_color.addItem('White', convertImage('colors/white.png'))
        glue_color.addItem('Pale-Yellow', convertImage('colors/pale-yellow.png'))
        glue_color.addItem('Yellow', convertImage('colors/yellow.png'))
        glue_color.addItem('Dark-yellow', convertImage('colors/dark-yellow.png'))

    glue_color.model.submitAll()

    grade = ref.section('grade')
    if lang == 'ru':
        grade.addItem('XF - Отличная/Люкс', convertImage('grade/lux.png'))
        grade.addItem('VF - Очень хорошая', convertImage('grade/vfine.png'))
        grade.addItem('F - Хорошая', convertImage('grade/fine.png'))
        grade.addItem('G/VG - Удовлетворительная', convertImage('grade/good.png'))
        grade.addItem('B - Плохая', convertImage('grade/bad.png'))
    else:
        grade.addItem('XF - Extra Fine/Luxury', convertImage('grade/lux.png'))
        grade.addItem('VF - Very Fine', convertImage('grade/vfine.png'))
        grade.addItem('F - Fine', convertImage('grade/fine.png'))
        grade.addItem('G/VG - Good/Very Good', convertImage('grade/good.png'))
        grade.addItem('B - Bad', convertImage('grade/bad.png'))

    grade.model.submitAll()

    stamp_color = ref.section('stamp_color')
    file = "reference/colors_%s.json" % lang
    if not os.path.isfile(file):
        file = "reference/colors_en.json"

    json_data = codecs.open(file, "r", "utf-8").read()
    colors = json.loads(json_data)
    file = "reference/colors_en.json"
    json_data = codecs.open(file, "r", "utf-8").read()
    colors_en = json.loads(json_data)
    for pos, color in enumerate(colors['colors']):
        color_name = colors_en['colors'][pos]
        icon_file = "colors/%s.png" % color_name.lower()
        stamp_color.addItem(colors['colors'][pos], convertImage(icon_file))

    stamp_color.model.submitAll()

    CAT_PATH = os.path.abspath(os.path.dirname(__file__))
    for category in ('grid', 'honeycomb', 'raster', 'watermark'):
        category_table = ref.section(category)
        file = "reference/" + category + "s_%s.json" % lang
        if not os.path.isfile(file):
            file = "reference/" + category + "s_en.json"

        json_data = codecs.open(file, "r", "utf-8").read()
        names = json.loads(json_data)
        file = "reference/" + category + "s_en.json"
        json_data = codecs.open(file, "r", "utf-8").read()
        names_en = json.loads(json_data)
        for pos, element in enumerate(names[category]):
            link = category + "s/%s.jpg" % names_en[category][pos]
            category_table.addItem(
                names[category][pos],
                None,
                os.path.join(CAT_PATH, link)
            )

    category_table.model.submitAll()

    ref.db.commit()

print("Done")
